﻿CREATE TRIGGER [Producator_Stoc_Critic]
   ON [Producator]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @tabela TABLE
    (
		Id_Medicament int,
		Checked bit DEFAULT(0)
    )
    
    DECLARE @id_Medicament int 
    DECLARE @stocCritic float
    
    INSERT INTO @tabela(Id_Medicament)
    SELECT Medicament.Id_Medicament
    FROM Medicament
    INNER JOIN Producator
    ON Medicament.Id_Producator = Producator.Id_Producator
    WHERE Producator.Id_Producator IN 
    (SELECT inserted.Id_Producator FROM inserted)

	SELECT TOP 1 @id_Medicament = Id_Medicament FROM @tabela WHERE Checked = 0
	
	WHILE @id_Medicament is not null
	BEGIN
		UPDATE @tabela
		SET Checked = 1
		WHERE Checked = 0 AND Id_Medicament = @id_Medicament
		
		EXEC CalculeazaStocCritic @id_Medicament,4,@stocCritic OUTPUT
		
		UPDATE Stoc_Critic
		SET Cantitate = @stocCritic
		WHERE Id_Medicament = @id_Medicament
		
		SET @id_Medicament = NULL;
		SELECT @id_Medicament = Id_Medicament FROM @tabela WHERE Checked = 0 
	END
END

